package com.zzyl.nursing.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zzyl.nursing.vo.DeviceDataListVo;
import org.apache.ibatis.annotations.Mapper;

import java.time.LocalDateTime;
import java.util.List;
import com.zzyl.nursing.domain.DeviceData;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * 设备数据Mapper接口
 * 
 * @author alexis
 * @date 2025-01-12
 */
@Mapper
public interface DeviceDataMapper extends BaseMapper<DeviceData>
{
    /**
     * 查询设备数据
     * 
     * @param id 设备数据主键
     * @return 设备数据
     */
    public DeviceData selectDeviceDataById(Long id);

    /**
     * 查询设备数据列表
     * 
     * @param deviceData 设备数据
     * @return 设备数据集合
     */
    public List<DeviceData> selectDeviceDataList(DeviceData deviceData);

    /**
     * 新增设备数据
     * 
     * @param deviceData 设备数据
     * @return 结果
     */
    public int insertDeviceData(DeviceData deviceData);

    /**
     * 修改设备数据
     * 
     * @param deviceData 设备数据
     * @return 结果
     */
    public int updateDeviceData(DeviceData deviceData);

    /**
     * 删除设备数据
     * 
     * @param id 设备数据主键
     * @return 结果
     */
    public int deleteDeviceDataById(Long id);

    /**
     * 批量删除设备数据
     * 
     * @param ids 需要删除的数据主键集合
     * @return 结果
     */
    public int deleteDeviceDataByIds(Long[] ids);
//    @Select("SELECT\n" +
//            "    DATE_FORMAT(alarm_time, '%H:00') AS dateTime,          -- 格式化时间为小时\n" +
//            "    AVG(data_value) AS    dataValue                        -- 计算每小时的平均值\n" +
//            "FROM\n" +
//            "    device_data\n" +
//            "WHERE\n" +
//            "    alarm_time >= #{startTime}                -- 设定查询的日期范围\n" +
//            "  AND alarm_time < #{endTime} and iot_id = #{iotId} and function_id = #{functionId} " +
//            "             -- 设定查询的日期范围\n" +
//            "GROUP BY\n" +
//            "    dateTime                                              -- 按小时分组\n" +
//            "ORDER BY\n" +
//            "    dateTime")

//    @Select("WITH RECURSIVE hour_series AS (\n" +
//            "    -- 递归生成从0到23小时的时间序列\n" +
//            "    SELECT 0 AS hour\n" +
//            "    UNION ALL\n" +
//            "    SELECT hour + 1\n" +
//            "    FROM hour_series\n" +
//            "    WHERE hour < 23\n" +
//            ")\n" +
//            "SELECT\n" +
//            "    H.hour,\n" +
//            "    IFNULL(AVG(DT.data_value), 0.0) AS avg_value\n" +
//            "FROM hour_series AS H\n" +
//            "         LEFT JOIN device_data AS DT\n" +
//            "                   ON H.hour = HOUR(DT.alarm_time)\n" +
//            "GROUP BY H.hour\n" +
//            "ORDER BY H.hour")
    @Select("select avg(data_value) from device_data\n" +
            "where iot_id = #{iotId} and function_id =#{functionId} and alarm_time between #{startTime} and #{endTime}")
    Long queryDeviceDataListByDay(@Param("iotId") String iotId, @Param("functionId") String functionId, @Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
}
